﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;

public partial class linliang_OrdersIssuedManagementOptimization : System.Web.UI.Page
{
    private SqlConnection con = DBTools.getDBConnection();

    protected void Page_Load(object sender, EventArgs e)
    {
        
    }
   
    protected void btnExport_Click(object sender, EventArgs e)
    {
        export();
    }
    private void FillRegionColumn(DataTable dt, DataTable dt1)
    {
        foreach (DataRow row in dt.Rows)
        {
            if (row["项目号"] != DBNull.Value)
            {
                foreach (DataRow row1 in dt1.Rows)
                {
                    if (row["项目号"].ToString() == row1["ProjectCode"].ToString())
                    {
                        row["区域"] = row1["RegionName"];
                        row["事务所"] = row1["DepartSwsName"];
                        break;
                    }
                }
            }
        }
    }

    private void AddRegionColumn(DataTable dataTable)
    {
        DataColumn col = new DataColumn("区域", Type.GetType("System.String"));
        dataTable.Columns.Add(col);
        col.SetOrdinal(0);

        col = new DataColumn("事务所", Type.GetType("System.String"));
        dataTable.Columns.Add(col);
        col.SetOrdinal(0);
    }
    private void export()
    {
        string userId = Session["userCode"].ToString();//员工编号
        //string userId = "11104940";       

        string sql = "select * from ShDcjYingyeRelationBase1;" +
            "select distinct A1.[Name] as [Warehouse_Name] from  CBO_Wh as A  left join [CBO_Wh_Trl] as A1 on (A1.SysMlFlag = 'zh-CN') and (A.[ID] = A1.[ID])  left join [Base_Organization] as A2 on (A.[Org] = A2.[ID])  left join [Base_Organization_Trl] as A3 on (A3.SysMlFlag = 'zh-CN') and (A2.[ID] = A3.[ID]) where A2.[Code]='300' and A.[Code] like '11%'";

        SqlDataAdapter ada = new SqlDataAdapter(sql, con);

        DataSet ds = new DataSet();
        ada.Fill(ds);

        string warehouse_names = "";
        foreach (DataRow item in ds.Tables[1].Rows)
        {
            warehouse_names += "[" + item[0].ToString() + "],";
        }
        warehouse_names = warehouse_names.Substring(0, warehouse_names.Length - 1);

        sql =
            "-- a:商贸驻外 a1:商贸成品 " +
            "-- b:制冷成品 c:股份成品 d:兰柯成品 e:芜湖自控成品 f:江西三花成品 " +
            "-- g:制冷未完成订单 h:股份未完成订单 i:兰柯未完成订单 j:芜湖自控未完成订单 k:江西三花未完成订单 " +
            "-- l:驻外库存明细" + Environment.NewLine;

        sql += "select aa2.Pro_Name 项目,aa2.[Pro_Code] 项目号,aa2.Item_ItemCode 料号,aa2.ItemSeg_Name 品名,aa2.ItemDescFlex_PubDescSeg1 型号," + warehouse_names + ",aa2.CommerceCanUseQty 商贸驻外库存小计,aa2.CommerceOutOnWayQty [商贸成品库存数(调出在途)],aa2.CommerceProductCanUseQty 商贸成品库存数,aa2.RefrigerationCanUseQty 制冷库存数,aa2.StockCanUseQty 股份库存数,aa2.RancoCanUseQty 新昌兰柯库存数,aa2.WuhuCanUseQty 芜湖三花自控库存数,aa2.JiangXiCanUseQty 江西三花制冷库存数,isnull(aa2.RefrigerationCanUseQty,0)+ISNULL(aa2.StockCanUseQty,0)+ISNULL(aa2.RancoCanUseQty,0)+ISNULL(aa2.WuhuCanUseQty,0)+isnull(aa2.JiangXiCanUseQty,0) 商贸子公司库存小计,aa2.RefrigerationNotPutInStorageQty 制冷未完成订单数,aa2.StockNotPutInStorageQty 股份未完成订单数,aa2.RancoNotPutInStorageQty 新昌兰柯未完成订单数,aa2.WuhuSelfControlNotPutInStorageQty 芜湖三花自控未完成订单数,aa2.JiangXiNotPutInStorageQty 江西三花制冷未完成订单数,isnull(aa2.RefrigerationNotPutInStorageQty,0)+isnull(aa2.StockNotPutInStorageQty,0)+isnull(aa2.RancoNotPutInStorageQty,0)+isnull(aa2.WuhuSelfControlNotPutInStorageQty,0)+isnull(aa2.JiangXiNotPutInStorageQty,0) 未完成订单小计,isnull(aa2.CommerceCanUseQty,0)+isnull(aa2.CommerceOutOnWayQty,0)+isnull(aa2.CommerceProductCanUseQty,0)+isnull(aa2.RefrigerationCanUseQty,0)+isnull(aa2.StockCanUseQty,0)+isnull(aa2.RancoCanUseQty,0)+isnull(aa2.WuhuCanUseQty,0)+isnull(aa2.JiangXiCanUseQty,0)+isnull(aa2.RefrigerationNotPutInStorageQty,0)+isnull(aa2.StockNotPutInStorageQty,0)+isnull(aa2.RancoNotPutInStorageQty,0)+isnull(aa2.WuhuSelfControlNotPutInStorageQty,0)+isnull(aa2.JiangXiNotPutInStorageQty,0) [订单、库存合计] from ( select isnull(aa1.Pro_Name,a2.Pro_Name) Pro_Name, ISNULL(aa1.[Pro_Code],a2.[Pro_Code]) [Pro_Code], ISNULL(aa1.Item_ItemCode,a2.Item_ItemCode) Item_ItemCode, isnull(aa1.ItemSeg_Name,a2.ItemSeg_Name) ItemSeg_Name, ISNULL(aa1.ItemDescFlex_PubDescSeg1,a2.ItemDescFlex_PubDescSeg1) ItemDescFlex_PubDescSeg1, aa1.CommerceCanUseQty, aa1.CommerceOutOnWayQty, a2.CommerceProductCanUseQty, aa1.RefrigerationCanUseQty, aa1.StockCanUseQty, aa1.RancoCanUseQty, aa1.WuhuCanUseQty, aa1.JiangXiCanUseQty, aa1.RefrigerationNotPutInStorageQty, aa1.StockNotPutInStorageQty, aa1.RancoNotPutInStorageQty, aa1.WuhuSelfControlNotPutInStorageQty, aa1.JiangXiNotPutInStorageQty from ( select isnull(ak.Pro_Name,a1.Pro_Name) Pro_Name, ISNULL(ak.[Pro_Code],a1.[Pro_Code]) [Pro_Code], ISNULL(ak.Item_ItemCode,a1.Item_ItemCode) Item_ItemCode, isnull(ak.ItemSeg_Name,a1.ItemSeg_Name) ItemSeg_Name, ISNULL(ak.ItemDescFlex_PubDescSeg1,a1.ItemDescFlex_PubDescSeg1) ItemDescFlex_PubDescSeg1, ak.CommerceCanUseQty, a1.CommerceOutOnWayQty, ak.RefrigerationCanUseQty, ak.StockCanUseQty, ak.RancoCanUseQty, ak.WuhuCanUseQty, ak.JiangXiCanUseQty, ak.RefrigerationNotPutInStorageQty, ak.StockNotPutInStorageQty, ak.RancoNotPutInStorageQty, ak.WuhuSelfControlNotPutInStorageQty, ak.JiangXiNotPutInStorageQty from ( select isnull(aj.Pro_Name,k.Pro_Name) Pro_Name, ISNULL(aj.[Pro_Code],k.[Pro_Code]) [Pro_Code], ISNULL(aj.Item_ItemCode,k.Item_ItemCode) Item_ItemCode, isnull(aj.ItemSeg_Name,k.ItemSeg_Name) ItemSeg_Name, ISNULL(aj.ItemDescFlex_PubDescSeg1,k.ItemDescFlex_PubDescSeg1) ItemDescFlex_PubDescSeg1, aj.CommerceCanUseQty, aj.RefrigerationCanUseQty, aj.StockCanUseQty, aj.RancoCanUseQty, aj.WuhuCanUseQty, aj.JiangXiCanUseQty JiangXiCanUseQty, aj.RefrigerationNotPutInStorageQty, aj.StockNotPutInStorageQty, aj.RancoNotPutInStorageQty, aj.WuhuSelfControlNotPutInStorageQty, k.JiangXiNotPutInStorageQty from ( select isnull(ai.Pro_Name,j.Pro_Name) Pro_Name, ISNULL(ai.[Pro_Code],j.[Pro_Code]) [Pro_Code], ISNULL(ai.Item_ItemCode,j.Item_ItemCode) Item_ItemCode, isnull(ai.ItemSeg_Name,j.ItemSeg_Name) ItemSeg_Name, ISNULL(ai.ItemDescFlex_PubDescSeg1,j.ItemDescFlex_PubDescSeg1) ItemDescFlex_PubDescSeg1, ai.CommerceCanUseQty, ai.RefrigerationCanUseQty, ai.StockCanUseQty, ai.RancoCanUseQty, ai.WuhuCanUseQty, ai.JiangXiCanUseQty JiangXiCanUseQty, ai.RefrigerationNotPutInStorageQty, ai.StockNotPutInStorageQty, ai.RancoNotPutInStorageQty, j.WuhuSelfControlNotPutInStorageQty from ( select isnull(ah.Pro_Name,i.Pro_Name) Pro_Name, ISNULL(ah.[Pro_Code],i.[Pro_Code]) [Pro_Code], ISNULL(ah.Item_ItemCode,i.Item_ItemCode) Item_ItemCode, isnull(ah.ItemSeg_Name,i.ItemSeg_Name) ItemSeg_Name, ISNULL(ah.ItemDescFlex_PubDescSeg1,i.ItemDescFlex_PubDescSeg1) ItemDescFlex_PubDescSeg1, ah.CommerceCanUseQty, ah.RefrigerationCanUseQty, ah.StockCanUseQty, ah.RancoCanUseQty, ah.WuhuCanUseQty, ah.JiangXiCanUseQty JiangXiCanUseQty, ah.RefrigerationNotPutInStorageQty, ah.StockNotPutInStorageQty, i.RancoNotPutInStorageQty from ( select isnull(abcdefg.Pro_Name,h.Pro_Name) Pro_Name, ISNULL(abcdefg.[Pro_Code],h.[Pro_Code]) [Pro_Code], ISNULL(abcdefg.Item_ItemCode,h.Item_ItemCode) Item_ItemCode, isnull(abcdefg.ItemSeg_Name,h.ItemSeg_Name) ItemSeg_Name, ISNULL(abcdefg.ItemDescFlex_PubDescSeg1,h.ItemDescFlex_PubDescSeg1) ItemDescFlex_PubDescSeg1, abcdefg.CommerceCanUseQty, abcdefg.RefrigerationCanUseQty, abcdefg.StockCanUseQty, abcdefg.RancoCanUseQty, abcdefg.WuhuCanUseQty, abcdefg.JiangXiCanUseQty JiangXiCanUseQty, abcdefg.RefrigerationNotPutInStorageQty, h.StockNotPutInStorageQty from ( select isnull(abcdef.Pro_Name,g.Pro_Name) Pro_Name, ISNULL(abcdef.[Pro_Code],g.[Pro_Code]) [Pro_Code], ISNULL(abcdef.Item_ItemCode,g.Item_ItemCode) Item_ItemCode, isnull(abcdef.ItemSeg_Name,g.ItemSeg_Name) ItemSeg_Name, ISNULL(abcdef.ItemDescFlex_PubDescSeg1,g.ItemDescFlex_PubDescSeg1) ItemDescFlex_PubDescSeg1, abcdef.CommerceCanUseQty, abcdef.RefrigerationCanUseQty, abcdef.StockCanUseQty, abcdef.RancoCanUseQty, abcdef.WuhuCanUseQty, abcdef.JiangXiCanUseQty JiangXiCanUseQty, g.RefrigerationNotPutInStorageQty from ( select isnull(abcde.Pro_Name,f.Pro_Name) Pro_Name, ISNULL(abcde.[Pro_Code],f.[Pro_Code]) [Pro_Code], ISNULL(abcde.Item_ItemCode,f.Item_ItemCode) Item_ItemCode, isnull(abcde.ItemSeg_Name,f.ItemSeg_Name) ItemSeg_Name, ISNULL(abcde.ItemDescFlex_PubDescSeg1,f.ItemDescFlex_PubDescSeg1) ItemDescFlex_PubDescSeg1, abcde.CommerceCanUseQty, abcde.RefrigerationCanUseQty, abcde.StockCanUseQty, abcde.RancoCanUseQty, abcde.WuhuCanUseQty, f.JiangXiCanUseQty from ( select isnull(abcd.Pro_Name,e.Pro_Name) Pro_Name, ISNULL(abcd.[Pro_Code],e.[Pro_Code]) [Pro_Code], ISNULL(abcd.Item_ItemCode,e.Item_ItemCode) Item_ItemCode, isnull(abcd.ItemSeg_Name,e.ItemSeg_Name) ItemSeg_Name, ISNULL(abcd.ItemDescFlex_PubDescSeg1,e.ItemDescFlex_PubDescSeg1) ItemDescFlex_PubDescSeg1, abcd.CommerceCanUseQty, abcd.RefrigerationCanUseQty, abcd.StockCanUseQty, abcd.RancoCanUseQty, e.WuhuCanUseQty from ( select isnull(abc.Pro_Name,d.Pro_Name) Pro_Name, ISNULL(abc.[Pro_Code],d.[Pro_Code]) [Pro_Code], ISNULL(abc.Item_ItemCode,d.Item_ItemCode) Item_ItemCode, isnull(abc.ItemSeg_Name,d.ItemSeg_Name) ItemSeg_Name, ISNULL(abc.ItemDescFlex_PubDescSeg1,d.ItemDescFlex_PubDescSeg1) ItemDescFlex_PubDescSeg1, abc.CommerceCanUseQty, abc.RefrigerationCanUseQty, abc.StockCanUseQty, d.RancoCanUseQty from ( select isnull(ab.Pro_Name,c.Pro_Name) Pro_Name, ISNULL(ab.[Pro_Code],c.[Pro_Code]) [Pro_Code], ISNULL(ab.Item_ItemCode,c.Item_ItemCode) Item_ItemCode, isnull(ab.ItemSeg_Name,c.ItemSeg_Name) ItemSeg_Name, ISNULL(ab.ItemDescFlex_PubDescSeg1,c.ItemDescFlex_PubDescSeg1) ItemDescFlex_PubDescSeg1, ab.CommerceCanUseQty, ab.RefrigerationCanUseQty, c.StockCanUseQty from ( select isnull(a.Pro_Name,b.Pro_Name) Pro_Name, ISNULL(a.[Pro_Code],b.[Pro_Code]) [Pro_Code], ISNULL(a.Item_ItemCode,b.Item_ItemCode) Item_ItemCode, isnull(a.ItemSeg_Name,b.ItemSeg_Name) ItemSeg_Name, ISNULL(a.ItemDescFlex_PubDescSeg1,b.ItemDescFlex_PubDescSeg1) ItemDescFlex_PubDescSeg1, a.CanUseQty CommerceCanUseQty, b.RefrigerationCanUseQty from (SELECT A6.Name AS Pro_Name, A1.Code AS Pro_Code, A.ItemInfo_ItemCode AS Item_ItemCode, ISNULL(CASE WHEN A2.[ItemFormAttribute] IN (16, 22) THEN A.[ItemInfo_ItemName] ELSE A2.[Name] END, '') AS ItemSeg_Name, A2.DescFlexField_PubDescSeg1 AS ItemDescFlex_PubDescSeg1, SUM(A.StoreQty - A.ResvStQty - A.ResvOccupyStQty - CASE WHEN ((((A.[IsProdCancel] = 1) OR (A.[MO_EntityID] != 0)) OR A.[ProductDate] IS NOT NULL) OR (A.[WP_EntityID] != 0)) THEN A.[StoreQty] ELSE CONVERT(decimal(24, 9), 0) END) AS CanUseQty FROM InvTrans_WhQoh AS A LEFT OUTER JOIN CBO_Project AS A1 ON A.Project = A1.ID LEFT OUTER JOIN CBO_ItemMaster AS A2 ON A.ItemInfo_ItemID = A2.ID LEFT OUTER JOIN Base_UOM AS A3 ON A.StoreUOM = A3.ID LEFT OUTER JOIN Base_Organization AS A4 ON A.LogisticOrg = A4.ID LEFT OUTER JOIN CBO_Wh AS A5 ON A.Wh = A5.ID LEFT OUTER JOIN CBO_Project_Trl AS A6 ON A6.SysMlFlag = 'zh-CN' AND A1.ID = A6.ID LEFT OUTER JOIN CBO_ItemMaster_Trl AS A7 ON A7.SysMlFlag = 'zh-CN' AND A2.ID = A7.ID LEFT OUTER JOIN Base_UOM_Trl AS A8 ON A8.SysMlFlag = 'zh-CN' AND A3.ID = A8.ID WHERE (A2.Name IS NOT NULL) AND (A2.Name <> '') AND (A4.Code = N'300') AND (A5.Code LIKE N'11%') GROUP BY A6.Name, A1.Code, A.ItemInfo_ItemCode, ISNULL(CASE WHEN A2.[ItemFormAttribute] IN (16, 22) THEN A.[ItemInfo_ItemName] ELSE A2.[Name] END, ''), A7.DescFlexField_CombineName, A8.Name, A3.Round_Precision, A2.DescFlexField_PubDescSeg1 ) a full join (SELECT A6.Name AS Pro_Name, A1.Code AS Pro_Code, A.ItemInfo_ItemCode AS Item_ItemCode, ISNULL(CASE WHEN A2.[ItemFormAttribute] IN (16, 22) THEN A.[ItemInfo_ItemName] ELSE A2.[Name] END, '') AS ItemSeg_Name, A2.DescFlexField_PubDescSeg1 AS ItemDescFlex_PubDescSeg1, SUM(A.StoreQty - A.ResvStQty - A.ResvOccupyStQty - CASE WHEN ((((A.[IsProdCancel] = 1) OR (A.[MO_EntityID] != 0)) OR A.[ProductDate] IS NOT NULL) OR (A.[WP_EntityID] != 0)) THEN A.[StoreQty] ELSE CONVERT(decimal(24, 9), 0) END) AS RefrigerationCanUseQty FROM InvTrans_WhQoh AS A LEFT OUTER JOIN CBO_Project AS A1 ON A.Project = A1.ID LEFT OUTER JOIN CBO_ItemMaster AS A2 ON A.ItemInfo_ItemID = A2.ID LEFT OUTER JOIN Base_UOM AS A3 ON A.StoreUOM = A3.ID LEFT OUTER JOIN Base_Organization AS A4 ON A.LogisticOrg = A4.ID LEFT OUTER JOIN CBO_Wh AS A5 ON A.Wh = A5.ID LEFT OUTER JOIN CBO_Project_Trl AS A6 ON A6.SysMlFlag = 'zh-CN' AND A1.ID = A6.ID LEFT OUTER JOIN CBO_ItemMaster_Trl AS A7 ON A7.SysMlFlag = 'zh-CN' AND A2.ID = A7.ID LEFT OUTER JOIN Base_UOM_Trl AS A8 ON A8.SysMlFlag = 'zh-CN' AND A3.ID = A8.ID WHERE (A2.Name IS NOT NULL) AND (A2.Name <> '') AND (A4.Code = N'200') AND (A5.Code IN (N'1000', N'1001')) and A1.Code is not null GROUP BY A6.Name, A1.Code, A.ItemInfo_ItemCode, ISNULL(CASE WHEN A2.[ItemFormAttribute] IN (16, 22) THEN A.[ItemInfo_ItemName] ELSE A2.[Name] END, ''), A7.DescFlexField_CombineName, A8.Name, A3.Round_Precision, A2.DescFlexField_PubDescSeg1, A2.NameSegment1 ) b on a.Pro_Code=b.Pro_Code and a.Item_ItemCode=b.Item_ItemCode) ab full join (SELECT A6.Name AS Pro_Name, A1.Code AS Pro_Code, A.ItemInfo_ItemCode AS Item_ItemCode, ISNULL(CASE WHEN A2.[ItemFormAttribute] IN (16, 22) THEN A.[ItemInfo_ItemName] ELSE A2.[Name] END, '') AS ItemSeg_Name, A2.DescFlexField_PubDescSeg1 AS ItemDescFlex_PubDescSeg1, SUM(A.StoreQty - A.ResvStQty - A.ResvOccupyStQty - CASE WHEN ((((A.[IsProdCancel] = 1) OR (A.[MO_EntityID] != 0)) OR A.[ProductDate] IS NOT NULL) OR (A.[WP_EntityID] != 0)) THEN A.[StoreQty] ELSE CONVERT(decimal(24, 9), 0) END) AS StockCanUseQty FROM InvTrans_WhQoh AS A LEFT OUTER JOIN CBO_Project AS A1 ON A.Project = A1.ID LEFT OUTER JOIN CBO_ItemMaster AS A2 ON A.ItemInfo_ItemID = A2.ID LEFT OUTER JOIN Base_UOM AS A3 ON A.StoreUOM = A3.ID LEFT OUTER JOIN Base_Organization AS A4 ON A.LogisticOrg = A4.ID LEFT OUTER JOIN CBO_Wh AS A5 ON A.Wh = A5.ID LEFT OUTER JOIN CBO_Project_Trl AS A6 ON A6.SysMlFlag = 'zh-CN' AND A1.ID = A6.ID LEFT OUTER JOIN CBO_ItemMaster_Trl AS A7 ON A7.SysMlFlag = 'zh-CN' AND A2.ID = A7.ID LEFT OUTER JOIN Base_UOM_Trl AS A8 ON A8.SysMlFlag = 'zh-CN' AND A3.ID = A8.ID WHERE (A2.Name IS NOT NULL) AND (A2.Name <> '') AND (A4.Code = N'100') AND (A5.Code IN (N'1000', N'1001')) and A1.Code is not null GROUP BY A6.Name, A1.Code, A.ItemInfo_ItemCode, ISNULL(CASE WHEN A2.[ItemFormAttribute] IN (16, 22) THEN A.[ItemInfo_ItemName] ELSE A2.[Name] END, ''), A7.DescFlexField_CombineName, A8.Name, A3.Round_Precision, A2.DescFlexField_PubDescSeg1, A2.NameSegment1 ) c on ab.Pro_Code=c.Pro_Code and ab.Item_ItemCode=c.Item_ItemCode) abc full join (SELECT A6.Name AS Pro_Name, A1.Code AS Pro_Code, A.ItemInfo_ItemCode AS Item_ItemCode, ISNULL(CASE WHEN A2.[ItemFormAttribute] IN (16, 22) THEN A.[ItemInfo_ItemName] ELSE A2.[Name] END, '') AS ItemSeg_Name, A2.DescFlexField_PubDescSeg1 AS ItemDescFlex_PubDescSeg1, SUM(A.StoreQty - A.ResvStQty - A.ResvOccupyStQty - CASE WHEN ((((A.[IsProdCancel] = 1) OR (A.[MO_EntityID] != 0)) OR A.[ProductDate] IS NOT NULL) OR (A.[WP_EntityID] != 0)) THEN A.[StoreQty] ELSE CONVERT(decimal(24, 9), 0) END) AS RancoCanUseQty FROM InvTrans_WhQoh AS A LEFT OUTER JOIN CBO_Project AS A1 ON A.Project = A1.ID LEFT OUTER JOIN CBO_ItemMaster AS A2 ON A.ItemInfo_ItemID = A2.ID LEFT OUTER JOIN Base_UOM AS A3 ON A.StoreUOM = A3.ID LEFT OUTER JOIN Base_Organization AS A4 ON A.LogisticOrg = A4.ID LEFT OUTER JOIN CBO_Wh AS A5 ON A.Wh = A5.ID LEFT OUTER JOIN CBO_Project_Trl AS A6 ON A6.SysMlFlag = 'zh-CN' AND A1.ID = A6.ID LEFT OUTER JOIN CBO_ItemMaster_Trl AS A7 ON A7.SysMlFlag = 'zh-CN' AND A2.ID = A7.ID LEFT OUTER JOIN Base_UOM_Trl AS A8 ON A8.SysMlFlag = 'zh-CN' AND A3.ID = A8.ID WHERE (A2.Name IS NOT NULL) AND (A2.Name <> '') AND (A4.Code = N'751') AND (A5.Code = N'1001') AND (A1.Code IS NOT NULL) GROUP BY A6.Name, A1.Code, A.ItemInfo_ItemCode, ISNULL(CASE WHEN A2.[ItemFormAttribute] IN (16, 22) THEN A.[ItemInfo_ItemName] ELSE A2.[Name] END, ''), A7.DescFlexField_CombineName, A8.Name, A3.Round_Precision, A2.DescFlexField_PubDescSeg1, A2.NameSegment1 ) d on abc.Pro_Code=d.Pro_Code and abc.Item_ItemCode=d.Item_ItemCode) abcd full join (SELECT A6.Name AS Pro_Name, A1.Code AS Pro_Code, A.ItemInfo_ItemCode AS Item_ItemCode, ISNULL(CASE WHEN A2.[ItemFormAttribute] IN (16, 22) THEN A.[ItemInfo_ItemName] ELSE A2.[Name] END, '') AS ItemSeg_Name, A2.DescFlexField_PubDescSeg1 AS ItemDescFlex_PubDescSeg1, SUM(A.StoreQty - A.ResvStQty - A.ResvOccupyStQty - CASE WHEN ((((A.[IsProdCancel] = 1) OR (A.[MO_EntityID] != 0)) OR A.[ProductDate] IS NOT NULL) OR (A.[WP_EntityID] != 0)) THEN A.[StoreQty] ELSE CONVERT(decimal(24, 9), 0) END) AS WuhuCanUseQty FROM InvTrans_WhQoh AS A LEFT OUTER JOIN CBO_Project AS A1 ON A.Project = A1.ID LEFT OUTER JOIN CBO_ItemMaster AS A2 ON A.ItemInfo_ItemID = A2.ID LEFT OUTER JOIN Base_UOM AS A3 ON A.StoreUOM = A3.ID LEFT OUTER JOIN Base_Organization AS A4 ON A.LogisticOrg = A4.ID LEFT OUTER JOIN CBO_Wh AS A5 ON A.Wh = A5.ID LEFT OUTER JOIN CBO_Project_Trl AS A6 ON A6.SysMlFlag = 'zh-CN' AND A1.ID = A6.ID LEFT OUTER JOIN CBO_ItemMaster_Trl AS A7 ON A7.SysMlFlag = 'zh-CN' AND A2.ID = A7.ID LEFT OUTER JOIN Base_UOM_Trl AS A8 ON A8.SysMlFlag = 'zh-CN' AND A3.ID = A8.ID WHERE (A2.Name IS NOT NULL) AND (A2.Name <> '') AND (A4.Code = N'700') AND (A5.Code = N'106') AND (A1.Code IS NOT NULL) GROUP BY A6.Name, A1.Code, A.ItemInfo_ItemCode, ISNULL(CASE WHEN A2.[ItemFormAttribute] IN (16, 22) THEN A.[ItemInfo_ItemName] ELSE A2.[Name] END, ''), A7.DescFlexField_CombineName, A8.Name, A3.Round_Precision, A2.DescFlexField_PubDescSeg1 ) e on abcd.Pro_Code=e.Pro_Code and abcd.Item_ItemCode=e.Item_ItemCode) abcde full join (SELECT A6.Name AS Pro_Name, A1.Code AS Pro_Code, A.ItemInfo_ItemCode AS Item_ItemCode, ISNULL(CASE WHEN A2.[ItemFormAttribute] IN (16, 22) THEN A.[ItemInfo_ItemName] ELSE A2.[Name] END, '') AS ItemSeg_Name, A2.DescFlexField_PubDescSeg1 AS ItemDescFlex_PubDescSeg1, SUM(A.StoreQty - A.ResvStQty - A.ResvOccupyStQty - CASE WHEN ((((A.[IsProdCancel] = 1) OR (A.[MO_EntityID] != 0)) OR A.[ProductDate] IS NOT NULL) OR (A.[WP_EntityID] != 0)) THEN A.[StoreQty] ELSE CONVERT(decimal(24, 9), 0) END) AS JiangXiCanUseQty FROM InvTrans_WhQoh AS A LEFT OUTER JOIN CBO_Project AS A1 ON A.Project = A1.ID LEFT OUTER JOIN CBO_ItemMaster AS A2 ON A.ItemInfo_ItemID = A2.ID LEFT OUTER JOIN Base_UOM AS A3 ON A.StoreUOM = A3.ID LEFT OUTER JOIN Base_Organization AS A4 ON A.LogisticOrg = A4.ID LEFT OUTER JOIN CBO_Wh AS A5 ON A.Wh = A5.ID LEFT OUTER JOIN CBO_Project_Trl AS A6 ON A6.SysMlFlag = 'zh-CN' AND A1.ID = A6.ID LEFT OUTER JOIN CBO_ItemMaster_Trl AS A7 ON A7.SysMlFlag = 'zh-CN' AND A2.ID = A7.ID LEFT OUTER JOIN Base_UOM_Trl AS A8 ON A8.SysMlFlag = 'zh-CN' AND A3.ID = A8.ID WHERE (A2.Name IS NOT NULL) AND (A2.Name <> '') AND (A4.Code = N'720') AND (A5.Code = N'1001') AND (A1.Code IS NOT NULL) GROUP BY A6.Name, A1.Code, A.ItemInfo_ItemCode, ISNULL(CASE WHEN A2.[ItemFormAttribute] IN (16, 22) THEN A.[ItemInfo_ItemName] ELSE A2.[Name] END, ''), A7.DescFlexField_CombineName, A8.Name, A3.Round_Precision, A2.DescFlexField_PubDescSeg1 ) f on abcde.Pro_Code=f.Pro_Code and abcde.Item_ItemCode=f.Item_ItemCode) abcdef full join (SELECT B4.Name AS Pro_Name, B3.Code AS Pro_Code, B1.ItemInfo_ItemCode AS Item_ItemCode, B1.ItemInfo_ItemName AS ItemSeg_Name, B5.DescFlexField_PubDescSeg1 AS ItemDescFlex_PubDescSeg1, SUM(B1.OrderByQtyTU - CAST(B1.DescFlexField_PrivateDescSeg1 AS float)) AS RefrigerationNotPutInStorageQty FROM SM_SO AS B INNER JOIN SM_SOLine AS B1 ON B.ID = B1.SO LEFT OUTER JOIN SM_SODocType AS B2 ON B.DocumentType = B2.ID LEFT OUTER JOIN CBO_Project AS B3 ON B1.Project = B3.ID LEFT OUTER JOIN CBO_Project_Trl AS B4 ON B4.SysMlFlag = 'zh-CN' AND B3.ID = B4.ID LEFT OUTER JOIN CBO_ItemMaster AS B5 ON B1.ItemInfo_ItemID = B5.ID LEFT OUTER JOIN Base_UOM AS B6 ON B1.IU = B6.ID LEFT OUTER JOIN Base_UOM AS B7 ON B1.IBU = B7.ID LEFT OUTER JOIN Base_UOM AS B8 ON B1.PU = B8.ID LEFT OUTER JOIN Base_UOM AS B9 ON B1.PBU = B9.ID LEFT OUTER JOIN Base_UOM AS B10 ON B1.TU = B10.ID LEFT OUTER JOIN Base_UOM AS B11 ON B1.TBU = B11.ID LEFT OUTER JOIN Base_UOM AS B12 ON B1.CU = B12.ID LEFT OUTER JOIN Base_UOM AS B13 ON B1.CBU = B13.ID LEFT OUTER JOIN Base_UOM AS B14 ON B1.WeightUOM = B14.ID LEFT OUTER JOIN Base_UOM AS B15 ON B1.VolumeUOM = B15.ID LEFT OUTER JOIN Base_Currency AS B16 ON B1.CC = B16.ID LEFT OUTER JOIN Base_Currency AS B17 ON B.TC = B17.ID LEFT OUTER JOIN Base_Currency AS B18 ON B.AC = B18.ID LEFT OUTER JOIN Base_Currency AS B19 ON B.FC = B19.ID LEFT OUTER JOIN Base_Organization AS B20 ON B.Org = B20.ID WHERE (B.Cancel_Canceled = 0) AND (B20.Code = N'200') AND (B.Status = 3) AND (B1.Status = 3) AND (B2.IsConsign = 0) AND (B2.BusinessType = 449) AND (B.Org = 1001208319148284) AND (CASE WHEN B1.[DescFlexField_PrivateDescSeg2] IS NULL THEN 0 WHEN (B1.[DescFlexField_PrivateDescSeg2] = '') THEN 0 ELSE CONVERT(decimal(24, 9), B1.[DescFlexField_PrivateDescSeg2]) END > 0) AND (B3.Code IS NOT NULL) GROUP BY B3.Code, B4.Name, B1.ItemInfo_ItemCode, B5.DescFlexField_PubDescSeg1, B1.ItemInfo_ItemName ) g on abcdef.Pro_Code=g.Pro_Code and abcdef.Item_ItemCode=g.Item_ItemCode) abcdefg full join (SELECT B4.Name AS Pro_Name, B3.Code AS Pro_Code, B1.ItemInfo_ItemCode AS Item_ItemCode, B1.ItemInfo_ItemName AS ItemSeg_Name, B5.DescFlexField_PubDescSeg1 AS ItemDescFlex_PubDescSeg1, SUM(B1.OrderByQtyTU - CAST(B1.DescFlexField_PrivateDescSeg1 AS float)) AS StockNotPutInStorageQty FROM SM_SO AS B INNER JOIN SM_SOLine AS B1 ON B.ID = B1.SO LEFT OUTER JOIN SM_SODocType AS B2 ON B.DocumentType = B2.ID LEFT OUTER JOIN CBO_Project AS B3 ON B1.Project = B3.ID LEFT OUTER JOIN CBO_Project_Trl AS B4 ON B4.SysMlFlag = 'zh-CN' AND B3.ID = B4.ID LEFT OUTER JOIN CBO_ItemMaster AS B5 ON B1.ItemInfo_ItemID = B5.ID LEFT OUTER JOIN Base_UOM AS B6 ON B1.IU = B6.ID LEFT OUTER JOIN Base_UOM AS B7 ON B1.IBU = B7.ID LEFT OUTER JOIN Base_UOM AS B8 ON B1.PU = B8.ID LEFT OUTER JOIN Base_UOM AS B9 ON B1.PBU = B9.ID LEFT OUTER JOIN Base_UOM AS B10 ON B1.TU = B10.ID LEFT OUTER JOIN Base_UOM AS B11 ON B1.TBU = B11.ID LEFT OUTER JOIN Base_UOM AS B12 ON B1.CU = B12.ID LEFT OUTER JOIN Base_UOM AS B13 ON B1.CBU = B13.ID LEFT OUTER JOIN Base_UOM AS B14 ON B1.WeightUOM = B14.ID LEFT OUTER JOIN Base_UOM AS B15 ON B1.VolumeUOM = B15.ID LEFT OUTER JOIN Base_Currency AS B16 ON B1.CC = B16.ID LEFT OUTER JOIN Base_Currency AS B17 ON B.TC = B17.ID LEFT OUTER JOIN Base_Currency AS B18 ON B.AC = B18.ID LEFT OUTER JOIN Base_Currency AS B19 ON B.FC = B19.ID LEFT OUTER JOIN Base_Organization AS B20 ON B.Org = B20.ID WHERE (B.Cancel_Canceled = 0) AND (B20.Code = N'100') AND (B.Status = 3) AND (B1.Status = 3) AND (B2.IsConsign = 0) AND (B2.BusinessType = 449) AND (B.Org = 1001111093917683) AND (CASE WHEN B1.[DescFlexField_PrivateDescSeg2] IS NULL THEN 0 WHEN (B1.[DescFlexField_PrivateDescSeg2] = '') THEN 0 ELSE CONVERT(decimal(24, 9), B1.[DescFlexField_PrivateDescSeg2]) END > 0) AND (B3.Code IS NOT NULL) GROUP BY B3.Code, B4.Name, B1.ItemInfo_ItemCode, B5.DescFlexField_PubDescSeg1, B1.ItemInfo_ItemName ) h on abcdefg.Pro_Code=h.Pro_Code and abcdefg.Item_ItemCode=h.Item_ItemCode) ah full join (SELECT B4.Name AS Pro_Name, B3.Code AS Pro_Code, B1.ItemInfo_ItemCode AS Item_ItemCode, B1.ItemInfo_ItemName AS ItemSeg_Name, B5.DescFlexField_PubDescSeg1 AS ItemDescFlex_PubDescSeg1, SUM(B1.OrderByQtyTU - CAST(B1.DescFlexField_PrivateDescSeg1 AS float)) AS RancoNotPutInStorageQty FROM SM_SO AS B INNER JOIN SM_SOLine AS B1 ON B.ID = B1.SO LEFT OUTER JOIN SM_SODocType AS B2 ON B.DocumentType = B2.ID LEFT OUTER JOIN CBO_Project AS B3 ON B1.Project = B3.ID LEFT OUTER JOIN CBO_Project_Trl AS B4 ON B4.SysMlFlag = 'zh-CN' AND B3.ID = B4.ID LEFT OUTER JOIN CBO_ItemMaster AS B5 ON B1.ItemInfo_ItemID = B5.ID LEFT OUTER JOIN Base_UOM AS B6 ON B1.IU = B6.ID LEFT OUTER JOIN Base_UOM AS B7 ON B1.IBU = B7.ID LEFT OUTER JOIN Base_UOM AS B8 ON B1.PU = B8.ID LEFT OUTER JOIN Base_UOM AS B9 ON B1.PBU = B9.ID LEFT OUTER JOIN Base_UOM AS B10 ON B1.TU = B10.ID LEFT OUTER JOIN Base_UOM AS B11 ON B1.TBU = B11.ID LEFT OUTER JOIN Base_UOM AS B12 ON B1.CU = B12.ID LEFT OUTER JOIN Base_UOM AS B13 ON B1.CBU = B13.ID LEFT OUTER JOIN Base_UOM AS B14 ON B1.WeightUOM = B14.ID LEFT OUTER JOIN Base_UOM AS B15 ON B1.VolumeUOM = B15.ID LEFT OUTER JOIN Base_Currency AS B16 ON B1.CC = B16.ID LEFT OUTER JOIN Base_Currency AS B17 ON B.TC = B17.ID LEFT OUTER JOIN Base_Currency AS B18 ON B.AC = B18.ID LEFT OUTER JOIN Base_Currency AS B19 ON B.FC = B19.ID LEFT OUTER JOIN Base_Organization AS B20 ON B.Org = B20.ID WHERE (B.Cancel_Canceled = 0) AND (B20.Code = N'751') AND (B.Status = 3) AND (B1.Status = 3) AND (B2.IsConsign = 0) AND (B2.BusinessType = 449) AND (B.Org = 1001609118255985) AND (CASE WHEN B1.[DescFlexField_PrivateDescSeg2] IS NULL THEN 0 WHEN (B1.[DescFlexField_PrivateDescSeg2] = '') THEN 0 ELSE CONVERT(decimal(24, 9), B1.[DescFlexField_PrivateDescSeg2]) END > 0) AND (B3.Code IS NOT NULL) GROUP BY B3.Code, B4.Name, B1.ItemInfo_ItemCode, B5.DescFlexField_PubDescSeg1, B1.ItemInfo_ItemName ) i on ah.Pro_Code=i.Pro_Code and ah.Item_ItemCode=i.Item_ItemCode) ai full join (SELECT B4.Name AS Pro_Name, B3.Code AS Pro_Code, B1.ItemInfo_ItemCode AS Item_ItemCode, B1.ItemInfo_ItemName AS ItemSeg_Name, B5.DescFlexField_PubDescSeg1 AS ItemDescFlex_PubDescSeg1, SUM(CAST(B1.DescFlexField_PrivateDescSeg2 AS float)) AS WuhuSelfControlNotPutInStorageQty FROM SM_SO AS B INNER JOIN SM_SOLine AS B1 ON B.ID = B1.SO LEFT OUTER JOIN SM_SODocType AS B2 ON B.DocumentType = B2.ID LEFT OUTER JOIN CBO_Project AS B3 ON B1.Project = B3.ID LEFT OUTER JOIN CBO_Project_Trl AS B4 ON B4.SysMlFlag = 'zh-CN' AND B3.ID = B4.ID LEFT OUTER JOIN CBO_ItemMaster AS B5 ON B1.ItemInfo_ItemID = B5.ID LEFT OUTER JOIN Base_UOM AS B6 ON B1.IU = B6.ID LEFT OUTER JOIN Base_UOM AS B7 ON B1.IBU = B7.ID LEFT OUTER JOIN Base_UOM AS B8 ON B1.PU = B8.ID LEFT OUTER JOIN Base_UOM AS B9 ON B1.PBU = B9.ID LEFT OUTER JOIN Base_UOM AS B10 ON B1.TU = B10.ID LEFT OUTER JOIN Base_UOM AS B11 ON B1.TBU = B11.ID LEFT OUTER JOIN Base_UOM AS B12 ON B1.CU = B12.ID LEFT OUTER JOIN Base_UOM AS B13 ON B1.CBU = B13.ID LEFT OUTER JOIN Base_UOM AS B14 ON B1.WeightUOM = B14.ID LEFT OUTER JOIN Base_UOM AS B15 ON B1.VolumeUOM = B15.ID LEFT OUTER JOIN Base_Currency AS B16 ON B1.CC = B16.ID LEFT OUTER JOIN Base_Currency AS B17 ON B.TC = B17.ID LEFT OUTER JOIN Base_Currency AS B18 ON B.AC = B18.ID LEFT OUTER JOIN Base_Currency AS B19 ON B.FC = B19.ID LEFT OUTER JOIN Base_Organization AS B20 ON B.Org = B20.ID WHERE (B.Cancel_Canceled = 0) AND (B20.Code = N'700') AND (B.Status = 3) AND (B1.Status = 3) AND (B2.IsConsign = 0) AND (B2.BusinessType = 449) AND (B.Org = 1001309061215398) AND (CASE WHEN B1.[DescFlexField_PrivateDescSeg2] IS NULL THEN 0 WHEN (B1.[DescFlexField_PrivateDescSeg2] = '') THEN 0 ELSE CONVERT(decimal(24, 9), B1.[DescFlexField_PrivateDescSeg2]) END > 0) AND (B3.Code IS NOT NULL) GROUP BY B3.Code, B4.Name, B1.ItemInfo_ItemCode, B5.DescFlexField_PubDescSeg1, B1.ItemInfo_ItemName ) j on ai.Pro_Code=j.Pro_Code and ai.Item_ItemCode=j.Item_ItemCode) aj full join (SELECT A4.Name AS Pro_Name, A3.Code AS Pro_Code, A1.ItemInfo_ItemCode AS Item_ItemCode, A1.ItemInfo_ItemName AS ItemSeg_Name, A6.DescFlexField_PubDescSeg1 AS ItemDescFlex_PubDescSeg1, SUM(CAST(A1.DescFlexField_PrivateDescSeg2 AS float)) AS JiangXiNotPutInStorageQty FROM SM_SO AS A INNER JOIN SM_SOLine AS A1 ON A.ID = A1.SO LEFT OUTER JOIN SM_SODocType AS A2 ON A.DocumentType = A2.ID LEFT OUTER JOIN CBO_Project AS A3 ON A1.Project = A3.ID LEFT OUTER JOIN Base_Language AS A5 ON A5.Code = 'zh-CN' AND A5.Effective_IsEffective = 1 LEFT OUTER JOIN CBO_Project_Trl AS A4 ON A4.SysMlFlag = 'zh-CN' AND A4.SysMlFlag = A5.Code AND A3.ID = A4.ID LEFT OUTER JOIN CBO_ItemMaster AS A6 ON A1.ItemInfo_ItemID = A6.ID LEFT OUTER JOIN Base_UOM AS A7 ON A1.IU = A7.ID LEFT OUTER JOIN Base_UOM AS A8 ON A1.IBU = A8.ID LEFT OUTER JOIN Base_UOM AS A9 ON A1.PU = A9.ID LEFT OUTER JOIN Base_UOM AS A10 ON A1.PBU = A10.ID LEFT OUTER JOIN Base_UOM AS A11 ON A1.TU = A11.ID LEFT OUTER JOIN Base_UOM AS A12 ON A1.TBU = A12.ID LEFT OUTER JOIN Base_UOM AS A13 ON A1.CU = A13.ID LEFT OUTER JOIN Base_UOM AS A14 ON A1.CBU = A14.ID LEFT OUTER JOIN Base_UOM AS A15 ON A1.WeightUOM = A15.ID LEFT OUTER JOIN Base_UOM AS A16 ON A1.VolumeUOM = A16.ID LEFT OUTER JOIN Base_Currency AS A17 ON A1.CC = A17.ID LEFT OUTER JOIN Base_Currency AS A18 ON A.TC = A18.ID LEFT OUTER JOIN Base_Currency AS A19 ON A.AC = A19.ID LEFT OUTER JOIN Base_Currency AS A20 ON A.FC = A20.ID LEFT OUTER JOIN Base_Organization AS A21 ON A.Org = A21.ID WHERE (A.Status = 3) AND (A1.Status = 3) AND (A21.Code = N'720') AND (A.Cancel_Canceled = 0) AND (CASE WHEN A1.[DescFlexField_PrivateDescSeg2] IS NULL THEN 0 WHEN (A1.[DescFlexField_PrivateDescSeg2] = '') THEN 0 ELSE CONVERT(decimal(24, 9), A1.[DescFlexField_PrivateDescSeg2]) END > 0) AND (A2.IsConsign = 0) AND (A2.BusinessType = 449) AND (A.Org = 1001512110099587) and A3.Code is not null GROUP BY A3.Code, A4.Name, A1.ItemInfo_ItemCode, A6.DescFlexField_PubDescSeg1, A1.ItemInfo_ItemName ) k on aj.Pro_Code=k.Pro_Code and aj.Item_ItemCode=k.Item_ItemCode) ak full join (SELECT A8.Name AS Pro_Name, A2.Code AS Pro_Code, A1.ItemInfo_ItemCode AS Item_ItemCode, A3.Name AS ItemSeg_Name, A3.DescFlexField_PubDescSeg1 AS ItemDescFlex_PubDescSeg1, SUM(A.StoreUOMQty - A.RcvQty) AS CommerceOutOnWayQty FROM InvDoc_TransOutSubLine AS A LEFT OUTER JOIN InvDoc_TransOutLine AS A1 ON A.TransOutLine = A1.ID LEFT OUTER JOIN CBO_Project AS A2 ON A1.Project = A2.ID LEFT OUTER JOIN CBO_ItemMaster AS A3 ON A1.ItemInfo_ItemID = A3.ID LEFT OUTER JOIN Base_UOM AS A4 ON A.StoreUOM = A4.ID LEFT OUTER JOIN InvDoc_TransferOut AS A5 ON A1.TransferOut = A5.ID LEFT OUTER JOIN Base_Organization AS A6 ON A1.TransOutOrg = A6.ID LEFT OUTER JOIN CBO_Wh AS A7 ON A1.TransOutWh = A7.ID LEFT OUTER JOIN CBO_Project_Trl AS A8 ON A8.SysMlFlag = 'zh-CN' AND A2.ID = A8.ID LEFT OUTER JOIN CBO_ItemMaster_Trl AS A9 ON A9.SysMlFlag = 'zh-CN' AND A3.ID = A9.ID LEFT OUTER JOIN Base_UOM_Trl AS A10 ON A10.SysMlFlag = 'zh-CN' AND A4.ID = A10.ID WHERE (A.BusiClose = 0) AND (A5.Status = 2) AND (A6.Code = N'300') AND (A7.Code LIKE N'10%') and A2.Code is not null GROUP BY A8.Name, A2.Code, A1.ItemInfo_ItemCode, A3.Name, A9.DescFlexField_CombineName, A10.Name, A4.Round_Precision, A3.DescFlexField_PubDescSeg1, A3.NameSegment1 ) a1 on ak.Pro_Code=a1.Pro_Code and ak.Item_ItemCode=a1.Item_ItemCode) aa1 full join (SELECT A6.Name AS Pro_Name, A1.Code AS Pro_Code, A.ItemInfo_ItemCode AS Item_ItemCode, ISNULL(CASE WHEN A2.[ItemFormAttribute] IN (16, 22) THEN A.[ItemInfo_ItemName] ELSE A2.[Name] END, '') AS ItemSeg_Name, A2.DescFlexField_PubDescSeg1 AS ItemDescFlex_PubDescSeg1, SUM(A.StoreQty - A.ResvStQty - A.ResvOccupyStQty - CASE WHEN ((((A.[IsProdCancel] = 1) OR (A.[MO_EntityID] != 0)) OR A.[ProductDate] IS NOT NULL) OR (A.[WP_EntityID] != 0)) THEN A.[StoreQty] ELSE CONVERT(decimal(24, 9), 0) END) AS CommerceProductCanUseQty FROM InvTrans_WhQoh AS A LEFT OUTER JOIN CBO_Project AS A1 ON A.Project = A1.ID LEFT OUTER JOIN CBO_ItemMaster AS A2 ON A.ItemInfo_ItemID = A2.ID LEFT OUTER JOIN Base_UOM AS A3 ON A.StoreUOM = A3.ID LEFT OUTER JOIN Base_Organization AS A4 ON A.LogisticOrg = A4.ID LEFT OUTER JOIN CBO_Wh AS A5 ON A.Wh = A5.ID LEFT OUTER JOIN CBO_Project_Trl AS A6 ON A6.SysMlFlag = 'zh-CN' AND A1.ID = A6.ID LEFT OUTER JOIN CBO_ItemMaster_Trl AS A7 ON A7.SysMlFlag = 'zh-CN' AND A2.ID = A7.ID LEFT OUTER JOIN Base_UOM_Trl AS A8 ON A8.SysMlFlag = 'zh-CN' AND A3.ID = A8.ID WHERE (A2.Name IS NOT NULL) AND (A2.Name <> '') AND (A4.Code = N'300') AND (A5.Code LIKE N'10%') and a1.code is not null GROUP BY A6.Name, A1.Code, A.ItemInfo_ItemCode, ISNULL(CASE WHEN A2.[ItemFormAttribute] IN (16, 22) THEN A.[ItemInfo_ItemName] ELSE A2.[Name] END, ''), A7.DescFlexField_CombineName, A8.Name, A3.Round_Precision, A2.DescFlexField_PubDescSeg1, A2.NameSegment1 ) a2 on aa1.Pro_Code=a2.Pro_Code and aa1.Item_ItemCode=a2.Item_ItemCode) aa2 left join (select * from (SELECT A7.Name AS Pro_Name, A2.Code AS Pro_Code, A.ItemInfo_ItemCode AS Item_ItemCode, ISNULL(CASE WHEN A3.[ItemFormAttribute] IN (16, 22) THEN A.[ItemInfo_ItemName] ELSE A3.[Name] END, '') AS ItemSeg_Name, A3.DescFlexField_PubDescSeg1 AS ItemDescFlex_PubDescSeg1, A10.Name AS Wh_Name, SUM(A.StoreQty - A.ResvStQty - A.ResvOccupyStQty - CASE WHEN ((((A.[IsProdCancel] = 1) OR (A.[MO_EntityID] != 0)) OR A.[ProductDate] IS NOT NULL) OR (A.[WP_EntityID] != 0)) THEN A.[StoreQty] ELSE CONVERT(decimal(24, 9), 0) END) AS CanUseQty FROM InvTrans_WhQoh AS A LEFT OUTER JOIN Base_UOM AS A1 ON A.StoreMainUOM = A1.ID LEFT OUTER JOIN CBO_Project AS A2 ON A.Project = A2.ID LEFT OUTER JOIN CBO_ItemMaster AS A3 ON A.ItemInfo_ItemID = A3.ID LEFT OUTER JOIN Base_UOM AS A4 ON A.StoreUOM = A4.ID LEFT OUTER JOIN CBO_Wh AS A5 ON A.Wh = A5.ID LEFT OUTER JOIN Base_Organization AS A6 ON A.LogisticOrg = A6.ID LEFT OUTER JOIN CBO_Project_Trl AS A7 ON A7.SysMlFlag = 'zh-CN' AND A2.ID = A7.ID LEFT OUTER JOIN CBO_ItemMaster_Trl AS A8 ON A8.SysMlFlag = 'zh-CN' AND A3.ID = A8.ID LEFT OUTER JOIN Base_UOM_Trl AS A9 ON A9.SysMlFlag = 'zh-CN' AND A4.ID = A9.ID LEFT OUTER JOIN CBO_Wh_Trl AS A10 ON A10.SysMlFlag = 'zh-CN' AND A5.ID = A10.ID WHERE (A3.Name IS NOT NULL) AND (A3.Name <> '') AND (A6.Code = N'300') AND (A5.Code LIKE N'11%') and A2.Code is not null GROUP BY A7.Name, A2.Code, A.ItemInfo_ItemCode, ISNULL(CASE WHEN A3.[ItemFormAttribute] IN (16, 22) THEN A.[ItemInfo_ItemName] ELSE A3.[Name] END, ''), A8.DescFlexField_CombineName, A3.DescFlexField_PubDescSeg1, A9.Name, A10.Name) p pivot(sum(canuseQty) for wh_name in (" + warehouse_names + ") ) as pvt ) l on aa2.Pro_Code=l.Pro_Code and aa2.Item_ItemCode=l.Item_ItemCode order by aa2.Pro_Name,aa2.ItemSeg_Name";
        

        ada.SelectCommand.CommandText = sql;
        ada.SelectCommand.CommandTimeout = 3000;
        ada.Fill(ds, "data");

        //添加国内国际列
        AddRegionColumn(ds.Tables["data"]);
        //填写列内容
        FillRegionColumn(ds.Tables["data"], ds.Tables[0]);

        string firmCode = string.Empty;//事务所编码
        DataRow[] rows;
        if (IsDirector(userId, ref firmCode))
        {
            rows = ds.Tables[0].Select("DepartSwsCode='" + firmCode + "'");

        }
        else
        {
            string salesmanCode = getSalesmanCode(userId);//业务员编码

            rows = ds.Tables[0].Select("SaleserCode='" + salesmanCode + "'");
        }

        if (rows.Length > 0)
        {
            string projectCodeSet = String.Empty;
            for (int i = 0; i < rows.Length; i++)
            {
                projectCodeSet += "'" + rows[i]["ProjectCode"].ToString().Trim() + "'";
                if (i != rows.Length - 1)
                {
                    projectCodeSet += ",";
                }
            }

            ds.Tables["data"].DefaultView.RowFilter = "项目号 in (" + projectCodeSet + ") and 项目号 is not null";
        }
        else
        {
            ds.Tables["data"].DefaultView.RowFilter = "项目号 is not null";
        }

        Util.ExportExcel(ds.Tables["data"].DefaultView, "库存及未完成订单" + DateTime.Now.ToString() + ".xlsx", "sheet1");
    }

  
    
    private bool IsDirector(string userId, ref string firmCode)
    {
        string sql = "select FirmCode from DirectorAndFirmRelation where DirectorOfTheEmployeeNumber='" + userId + "'";

        SqlCommand cmd = new SqlCommand(sql, con);
        con.Open();
        object result = cmd.ExecuteScalar();
        con.Close();
        if (result != null)
        {
            firmCode = result.ToString();
            return true;
        }
        return false;
    }
    
    private string getSalesmanCode(string userId)
    {
        string sql = "select case when Code=Tip then Code else Tip end   from base_user where Code='" + userId + "'";
        SqlConnection con = DBTools.getDBConnection();
        SqlCommand cmd = new SqlCommand(sql, con);
        con.Open();
        string SalesmanCode = cmd.ExecuteScalar().ToString();
        con.Close();
        return SalesmanCode;
    }
    
    
}